﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Collections;
using UFC.DBUtility;

namespace SendIndustrialNetwork
{
    public class CommonMethods
    {
        public enum OpType
        {
            新增 = 1,
            更新 = 2,
            删除 = 3
        }
        /// <summary>
        /// 插入到 sync_tablechangerecord
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="tableName"></param>
        /// <param name="uniquecolname"></param>
        /// <param name="toAppId"></param>
        public static void InsertTableChangeRecord(DataTable dt, string tableName, string uniquecolname, string toAppId, OpType type, string containerName = "")
        {
            tableName = tableName.ToUpper();
            uniquecolname = uniquecolname.ToUpper();
            string insertSql = @"INSERT INTO sync_tablechangerecord (id,tablename,uniquecolname,uniquecolvalue,operatetype,newvalue)values('{0}','{1}','{2}','{3}',{4},'" + containerName + "')";
            string insertSql2 = @"INSERT INTO sync_sendtoappid (parentid,toappid)values('{0}','{1}')";
            string tableInsertSql = @"INSERT INTO sync_tableinfo(tablename,uniquecolname) VALUES('{0}','{1}')";
            string updateSql2 = @"UPDATE sync_sendtoappid so SET so.ismeshandle = 0 WHERE so.id = '{0}'";
            ArrayList arrList = new ArrayList();
            //如果没有创建表结构，则创建
            if (OracleHelper.QueryTable(string.Format("SELECT 1 FROM sync_tableinfo st WHERE st.tablename = '{0}'", tableName), null).Rows.Count == 0)
            {
                arrList.Add(string.Format(tableInsertSql, tableName, uniquecolname));
            }
            switch (type)
            {
                case OpType.新增:
                    foreach (DataRow row in dt.Rows)
                    {
                        try
                        {
                            if (string.IsNullOrWhiteSpace(row[uniquecolname].ToString()))
                            {
                                continue;
                            }
                            var uniqueID = Guid.NewGuid().ToString();
                            DataTable tablechangerecord = OracleHelper.QueryTable(string.Format("SELECT ID FROM sync_tablechangerecord WHERE uniquecolvalue = '{0}' AND tablename='{1}' AND operatetype = {2}", row[uniquecolname], tableName, int.Parse(((Enum)type).ToString("D"))), null);
                            if (tablechangerecord.Rows.Count == 0)
                            {
                                arrList.Add(string.Format(insertSql, uniqueID, tableName, uniquecolname, row[uniquecolname], int.Parse(((Enum)type).ToString("D"))));
                            }
                            else
                            {
                                uniqueID = tablechangerecord.Rows[0]["ID"].ToString();
                            }
                            DataTable dtSendToApp = OracleHelper.QueryTable(string.Format(@"SELECT s.id FROM sync_sendtoappid s  
                                                            WHERE s.parentid = '{0}' AND s.toappid = '{1}'", uniqueID, toAppId), null);
                            if (dtSendToApp.Rows.Count == 0)
                            {
                                arrList.Add(string.Format(insertSql2, uniqueID, toAppId));
                            }
                            else
                            {
                                arrList.Add(string.Format(updateSql2, dtSendToApp.Rows[0]["id"].ToString())); //如果存在，则将其更新为未推送，让其再推一次（解决推送过，但是目标系统未收到的问题。）
                            }
                        }
                        catch (Exception ex) { continue; }
                    }
                    break;
                case OpType.更新:
                    foreach (DataRow row in dt.Rows)
                    {
                        try
                        {
                            if (string.IsNullOrWhiteSpace(row[uniquecolname].ToString()))
                            {
                                continue;
                            }
                            var uniqueID = Guid.NewGuid().ToString();
                            arrList.Add(string.Format(insertSql, uniqueID, tableName, uniquecolname, row[uniquecolname], int.Parse(((Enum)type).ToString("D"))));
                            arrList.Add(string.Format(insertSql2, uniqueID, toAppId));
                        }
                        catch (Exception ex) { continue; }
                    }
                    break;
                case OpType.删除:
                    foreach (DataRow row in dt.Rows)
                    {
                        try
                        {
                            if (string.IsNullOrWhiteSpace(row[uniquecolname].ToString()))
                            {
                                continue;
                            }
                            var uniqueID = Guid.NewGuid().ToString();
                            arrList.Add(string.Format(insertSql, uniqueID, tableName, uniquecolname, row[uniquecolname], int.Parse(((Enum)type).ToString("D"))));
                            arrList.Add(string.Format(insertSql2, uniqueID, toAppId));
                        }
                        catch (Exception ex) { continue; }
                    }
                    break;
                default:
                    break;
            }


            if (arrList.Count > 0)
            {
                OracleHelper.ExecuteSqlTran(arrList);
            }
        }

        /// <summary>
        /// 插入sync_tablechangerecord（特殊，会推送datatable所有数据）
        /// </summary>
        /// <param name="dtData"></param>
        /// <param name="tableName"></param>
        /// <param name="uniqueColName"></param>
        /// <param name="toAppId"></param>
        public static void InsertTableChangeRecord(DataTable dtData, string tableName, string uniqueColName, string toAppId)
        {
            tableName = tableName.ToUpper();
            uniqueColName = uniqueColName.ToUpper();
            string insertSql = "INSERT INTO sync_tablechangerecord (id,tablename,uniquecolname,uniquecolvalue,operatetype)values('{0}','{1}','{2}','{3}',{4})";
            string insertSql2 = "INSERT INTO sync_sendtoappid (parentid,toappid)values('{0}','{1}')";
            string tableInsertSql = "INSERT INTO sync_tableinfo(tablename,uniquecolname) VALUES('{0}','{1}')";
            ArrayList arrList = new ArrayList();
            if (OracleHelper.QueryTable(string.Format("SELECT 1 FROM sync_tableinfo st WHERE st.tablename = '{0}'", tableName), null).Rows.Count == 0)
            {
                arrList.Add(string.Format(tableInsertSql, tableName, uniqueColName));
            }
            foreach (DataRow row in dtData.Rows)
            {
                try
                {
                    if (string.IsNullOrWhiteSpace(row[uniqueColName].ToString()))
                    {
                        continue;
                    }
                    var uniqueID = Guid.NewGuid().ToString();
                    string sql = string.Format(@"
                                    SELECT 1 FROM sync_tablechangerecord st
                                    LEFT JOIN sync_sendtoappid so ON so.parentid = st.id
                                    WHERE st.uniquecolvalue = '{0}' AND st.tablename = '{1}' AND st.operatetype = 1 AND so.toappid = '{2}'", row[uniqueColName].ToString(), tableName, toAppId);
                    if (OracleHelper.QueryTable(sql, null).Rows.Count == 0)
                    {
                        DataTable tablechangerecord = OracleHelper.QueryTable(string.Format("SELECT ID FROM sync_tablechangerecord WHERE uniquecolvalue = '{0}' AND tablename='{1}' AND operatetype = {2}", row[uniqueColName].ToString(), tableName, 1), null);
                        if (tablechangerecord.Rows.Count == 0)
                        {
                            arrList.Add(string.Format(insertSql, uniqueID, tableName, uniqueColName, row[uniqueColName].ToString(), 1));
                        }
                        else
                        {
                            uniqueID = tablechangerecord.Rows[0]["ID"].ToString();
                        }
                        if (OracleHelper.QueryTable(string.Format(@"SELECT 1 FROM sync_sendtoappid s  
                                                            WHERE s.parentid = '{0}' AND s.toappid = '{1}'", uniqueID, toAppId), null).Rows.Count == 0)
                        {
                            arrList.Add(string.Format(insertSql2, uniqueID, toAppId));
                        }
                    }
                    else
                    {
                        // 如果存在新增推送记录，则推送更新
                        arrList.Add(string.Format(insertSql, uniqueID, tableName, uniqueColName, row[uniqueColName].ToString(), 2));
                        arrList.Add(string.Format(insertSql2, uniqueID, toAppId));
                    }
                }
                catch (Exception ex)
                {
                    continue;
                }
            }
            if (arrList.Count > 0)
            {
                OracleHelper.ExecuteSqlTran(arrList);
            }
        }

        /// <summary>
        /// 插入到 sync_tablechangerecord_rb（热表专用） add by zhangrj 20250728
        /// </summary>
        /// <param name="dt">数据源</param>
        /// <param name="tableName">表名</param>
        /// <param name="uniquecolname">表唯一列名</param>
        /// <param name="type">操作类型</param>
        public static void InsertTableChangeRecord_RB(DataTable dt, string tableName, string uniquecolname, OpType type)
        {
            tableName = tableName.ToUpper();
            uniquecolname = uniquecolname.ToUpper();
            string insertSql = @"INSERT INTO sync_tablechangerecord_rb (id,tablename,uniquecolname,uniquecolvalue,createtime,operatetype,ismeshandle) values('{0}','{1}','{2}','{3}',SYSDATE,{4},0)";//插入sql
            string updateSql = @"UPDATE sync_tablechangerecord_rb st SET st.ismeshandle = 0 WHERE st.id = '{0}'";//更新其为未推送sql
            string tableInsertSql = @"INSERT INTO sync_tableinfo(tablename,uniquecolname) VALUES('{0}','{1}')";
            ArrayList arrList = new ArrayList();
            //如果没有创建表结构，则创建
            if (OracleHelper.QueryTable(string.Format("SELECT 1 FROM sync_tableinfo st WHERE st.tablename = '{0}'", tableName), null).Rows.Count == 0)
            {
                arrList.Add(string.Format(tableInsertSql, tableName, uniquecolname));
            }
            foreach (DataRow row in dt.Rows) //循环数据源，开始添加到推送表
            {
                if (string.IsNullOrWhiteSpace(row[uniquecolname].ToString()))//如果表唯一列没有值，跳过不推送
                {
                    continue;
                }
                DataTable tablechangerecord = OracleHelper.QueryTable(string.Format("SELECT ID FROM sync_tablechangerecord_rb WHERE uniquecolvalue = '{0}' AND tablename='{1}' AND operatetype = {2}", row[uniquecolname], tableName, int.Parse(((Enum)type).ToString("D"))), null);
                if (tablechangerecord.Rows.Count == 0)//如果不存在，则插入
                {
                    arrList.Add(string.Format(insertSql, Guid.NewGuid().ToString(), tableName, uniquecolname, row[uniquecolname], int.Parse(((Enum)type).ToString("D"))));
                }
                else
                {
                    arrList.Add(string.Format(updateSql, tablechangerecord.Rows[0]["ID"].ToString()));//如果存在，则将其更新为未推送，让其再推一次
                }
            }
            OracleHelper.ExecuteSqlTran(arrList);
        }
    }
}
